package com.eastdigit.system.model;

import com.eastdigit.data.DataSourceId;
import com.eastdigit.servlet.ReqBean;
import com.eastdigit.system.service.SpringServiceFactory;
import com.eastdigit.util.StringUtil;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2015/12/7.
 */
public class SqlBuilder {

    private StringBuilder sql = new StringBuilder();
    private List<Object> params = new ArrayList<Object>();
    private DataSourceId dataSource;

    // private UserStateCache userStateCache;

    public SqlBuilder() {
        dataSource = SpringServiceFactory.getInstance("dataSourceId", DataSourceId.class);
        // userStateCache = SpringServiceFactory.getInstance("userStateCache",
        // UserStateCache.class);
    }

    public SqlBuilder(String sql, Object... params) {
        this();
        this.sql.append(sql);
        if (params != null) {
            for (Object param : params) {
                this.params.add(param);
            }
        }
    }

    public SqlBuilder appendSql(String sql, Object... params) {
        this.sql.append(sql);
        if (params != null) {
            for (Object param : params) {
                this.params.add(param);
            }
        }
        return this;
    }

    public SqlBuilder concatSql(String... filed) {
        if (filed != null && filed.length > 0) {
            String dataBase = dataSource.getDataBase();
            if (null != dataBase && dataBase.contains("mysql")) {
                this.sql.append(" CONCAT(");
                for (int i = 0; i < filed.length; i++) {
                    this.sql.append(filed[i]);
                    if (i != filed.length - 1) {
                        this.sql.append(", ");
                    }
                }
                this.sql.append(")");
            } else if (null != dataBase && dataBase.contains("oracle")) {
                this.sql.append("(");
                for (int i = 0; i < filed.length; i++) {
                    this.sql.append(filed[i]);
                    if (i != filed.length - 1) {
                        this.sql.append(" || ");
                    }
                }
                this.sql.append(")");
            }
        }
        return this;
    }

    protected SqlBuilder addWhere(String fieldName, String value, String strOperate, String fieldType) {
        String dataBase = dataSource.getDataBase();
        if (StringUtil.isEmpty(value)) {
            return this;
        }
        if (StringUtil.isEmpty(fieldType))
            fieldType = "0";
        if (StringUtil.isEmpty(strOperate))
            strOperate = "=";
        if ("0".equals(fieldType)) { // 0:字符型
            if ("like".equals(strOperate)) {
                this.sql.append(" and " + fieldName.trim() + " like ? ");
                this.params.add(value.trim() + "%");
            } else if ("like2".equals(strOperate)) {
                this.sql.append(" and " + fieldName.trim() + " like ? ");
                this.params.add("%" + value.trim() + "%");
            } else {
                this.sql.append(" and " + fieldName.trim() + strOperate.trim() + " ? ");
                this.params.add(value.trim());
            }
        } else if ("1".equals(fieldType)) { // 1:数字型
            this.sql.append(" and " + fieldName.trim() + strOperate.trim() + " ? ");
            this.params.add(value.trim());
        } else if ("2".equals(fieldType)) { // 2:日期型
            if (null != dataBase && dataBase.contains("mysql")) {
                this.sql.append(" and " + fieldName.trim() + strOperate.trim() + " ? ");
                this.params.add(value.trim());
            } else if (null != dataBase && dataBase.contains("oracle")) {
                this.sql.append(" and " + fieldName.trim() + strOperate.trim() + "to_date(?, 'yyyy-mm-dd hh24:mi:ss') ");
                this.params.add(value.trim());
            }
        }
        return this;
    }

    public SqlBuilder addWhere(String fieldName, String value, String strOperate) {
        return addWhere(fieldName, value, strOperate, "0");
    }

    public SqlBuilder addWhere(String fieldName, String value) {
        return addWhere(fieldName, value, "=", "0");
    }

    public SqlBuilder addTimeWhere(String fieldName, String value) {
        return addWhere(fieldName, value, "=", "2");
    }

    public SqlBuilder addTimeWhere(String fieldName, String value, String strOperate) {
        return addWhere(fieldName, value, strOperate, "2");
    }

    public SqlBuilder addTimeBeginWhere(String fieldName, String value) {
        return addWhere(fieldName, value, ">=", "2");
    }

    public SqlBuilder addTimeEndWhere(String fieldName, String value) {
        if (StringUtil.isNotEmpty(value) && value.length() == 10) {
            value += " 23:59:59";
        }
        return addWhere(fieldName, value, "<=", "2");
    }

    public SqlBuilder addWhereLike(String fieldName, String value) {
        return addWhere(fieldName, value, "like", "0");
    }

    public SqlBuilder addWhereLike2(String fieldName, String value) {
        return addWhere(fieldName, value, "like2", "0");
    }

    public SqlBuilder addWhereIn(String fieldName, List<Object> values) {
        return this.addWhereIn(fieldName, values.toArray());
    }

    public SqlBuilder addWhereIn(String fieldName, Object[] values) {
        // if (values != null && values.length > 0) {
        sql.append(" and ").append(fieldName).append(" in (");
        for (int i = 0; i < values.length; i++) {
            sql.append("?");
            if (i != values.length - 1) {
                sql.append(", ");
            }
            this.params.add(values[i]);
        }
        sql.append(")");
        // }
        return this;
    }

    /**
     * 添加数据范围
     * 
     * @param deptColumn
     * @param reqBean
     * @return
     */
    public SqlBuilder addWhereDataScope(String deptColumn, ReqBean reqBean) {
        if (!deptColumn.contains(".")) {
            throw new BusinessException("请给添加别名");
        }
        String userId = reqBean.getUserId(), menuId = reqBean.getMenuId();
        if (!Constants.SUPER_USER_ID.equals(userId) /*
                                                     * &&
                                                     * StringUtil.isNotEmpty(userId
                                                     * ) &&
                                                     * StringUtil.isNotEmpty
                                                     * (menuId)
                                                     */) {
            this.sql.append(" and exists(select 1 from SYS_DATA_SCOPE where DEPT_ID = ").append(deptColumn)
                    .append(" and USER_ID = ? and MENU_ID = ?)");
            this.params.add(userId);
            this.params.add(menuId);
        }
        return this;
    }

    public SqlBuilder orderBy(String orderBy) {
        sql.append(" ").append(orderBy);
        return this;
    }

    public String getSql() {
        return sql.toString();
    }

    public Object[] getParams() {
        return params.toArray();
    }
}
